﻿//--------------------------------------------------------------------------
// 
//  Copyright (c) Chili Software.  All rights reserved. 
// 
//  File: MsSqlStorage.cs
//
//  Description: Provides a storage class for Microsoft SQL Server.
// 
//--------------------------------------------------------------------------

using System;
using System.Reflection;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Chili.Opf3.Query;
using System.Data.SqlTypes;
using Chili.Opf3.Query.OPathParser;

namespace Chili.Opf3.Storages.MsSql
{
	/// <summary>Storage class that encapsulates a pyhsical Ms Sql Server database.</summary>
	/// <remarks>
	/// 	<para>This class encapsulates a physical Ms Sql Server database. It is used to access to
	///     an Ms Sql Server database that is installed somewhere on your or on a remote machine. The storage
	///     implements the basic interfaces, such as
	///     <see cref="Opf3.Storages.IStorage">IStorage</see> for the basic storage IO
	///     functinality and
	///     <see cref="Opf3.Storages.ITransactionStorage">ITransactionStorage</see> for the
	///     transaction management.</para>
	/// 	<para><b>Attention:</b> You should not access the methods of the storage directly
	///     but instead register the storage with your instances of the
	///     <see cref="Opf3.ObjectContext">ObjectContext</see>. The
	///     <see cref="Opf3.ObjectContext">ObjectContext</see> accesses the methods internally
	///     to get the objects from the storage.</para>
	/// </remarks>
	/// <example>
	/// The following example shows how you should use the Ms Sql Server 2000/2005 storage in your 
	/// applications.
	/// <code lang="CS">
	/// // ... Other code
	/// 
	/// // Creates a new ObjectContext that uses an Ms Sql Server database as storage.
	/// // You should only pass an instance of the storage to your ObjectContext.
	/// // That's it! Nothing more. The methods of the storage are accessed
	/// // by the context internally.
	/// ObjectContext context = new ObjectContext(
	///		new MsSqlStorage("... some ConnectionString ..."));
	/// 
	/// // ... Other code
	/// </code>
	/// </example>
	public class MsSqlStorage : SqlStorageBase
	{
		private const int _constraintError = 3316;
		private const int _uniqueConstraintViolation = 2627;
		private const int _foreignKeyViolation1 = 3201;
		private const int _foreignKeyViolation2 = 547;
		private const int _primaryKeyViolation = 3022;
		private const int _indexViolation = 2601;

		/// <summary>
		/// Creates a new instance of the <see cref="MsSqlStorage">MsSqlStorage Class</see>.
		/// </summary>
		/// <param name="connectionString">
		/// Specifies a string that contains the information (for ADO.NET) to connect to storage.
		/// </param>
		/// <exception cref="ArgumentNullException">ConnectionString is null.</exception>
		public MsSqlStorage(string connectionString) : base(connectionString)
		{
			this.StorageCommandBuilders.Add(new MsSqlCommandBuilder());
			this.StorageCommandBuilders.Add(new SqlQueryCommandBuilder());
		}

		/// <summary>
		/// Creates a new instance of the <see cref="MsSqlStorage">MsSqlStorage Class</see>.
		/// </summary>
		/// <param name="user">Specifies the name of the user that connects to the storage.</param>
		/// <param name="password">Specifies the password of the user that connects to the storage.</param>
		/// <param name="host">Specifies the hostname of the machine where the database is installed.</param>
		/// <param name="database">Specifies the name of the database that you want connect to.</param>
		/// <exception cref="ArgumentNullException">User is null, password is null and database is null.</exception>
		public MsSqlStorage(string user, string password, string host, string database)
			: this(string.Format(@"user id={0};server={1};database={2};password={3}", user, host, database, password))
		{
			if (user == null)
				throw new ArgumentNullException("user");
			if (password == null)
				throw new ArgumentNullException("password");
			if (database == null)
				throw new ArgumentNullException("database");
			if (host == null)
				throw new ArgumentNullException("host");
		}

		#region Overriden Members

		/// <summary>
		/// Executes a command on the storage that does not expect any results. It the method
		/// returns an argument it is used by the framework to set the
		/// <see cref="Opf3.FieldAttribute.AutoNumber">AutoNumber</see> property of the persistent
		/// object.
		/// </summary>
		/// <param name="query">
		/// 	<see cref="Opf3.Query.IQuery">IQuery</see> that is compiled and executed on the
		/// storage.
		/// </param>
		/// <exception cref="Opf3.Storages.StorageConstraintException">A constraint of the storage 
		/// has been violated.</exception>
		/// <exception cref="Opf3.Storages.ForeignKeyViolationException">A foreign key in the storage 
		/// has been violated.</exception>
		/// <exception cref="Opf3.Storages.PrimaryKeyViolationException">A duplicate primary key 
		/// is inserted in the storage.</exception>
		/// <exception cref="Opf3.Storages.PrimaryKeyViolationException">An index has been violated.</exception>
		/// <exception cref="Opf3.Storages.StorageException">Any other unexpected storage exception.</exception>
		/// <exception cref="ArgumentNullException">Query is null.</exception>
		public override object ExecuteCommand(IQuery query)
		{
			if (query == null)
				throw new ArgumentNullException("query");

			try
			{
				object autoValue = null;
				CompiledQuery compiledQuery = query.ToCompiledQuery(this);
				Tracing.Current.TraceStorageIO(compiledQuery);

				// Prepare the command for execution.
				using (IDbCommand dbCommand = this.PrepareCommand(compiledQuery))
				{
					// Check if we have to add the scope_identity flag to get the identity value.
					if (query.QueryType == QueryTypes.Insert && compiledQuery.AutoNumberItems != null)
					{
						dbCommand.CommandText = string.Format("{0} SELECT SCOPE_IDENTITY()", dbCommand.CommandText);
						autoValue = dbCommand.ExecuteScalar();

						Tracing.Current.TraceStorageIO("Autonumber value returned: " + autoValue);
					}
					else
					{
						// Execute the command without fetching the identity value. Not required, since we have no 
						// properties marked with AutoNumber = true.
						dbCommand.ExecuteNonQuery();
					}

					// If no transaction is active we can close the connection after executing the command.
					if (!IsTransactionActive)
						dbCommand.Connection.Close();
				}
				return autoValue;
			}
			catch (SqlException ex)
			{
				if (ex.Errors[0].Number == _constraintError || ex.Errors[0].Number == _uniqueConstraintViolation)
					throw new StorageConstraintException(ex.Message, ex, query);
				else if (ex.Errors[0].Number == _foreignKeyViolation1 || ex.Errors[0].Number == _foreignKeyViolation2)
					throw new ForeignKeyViolationException(ex.Message, ex, query);
				else if (ex.Errors[0].Number == _primaryKeyViolation)
					throw new PrimaryKeyViolationException(ex.Message, ex, query);
				else if (ex.Errors[0].Number == _indexViolation)
					throw new IndexViolationException(ex.Message, ex, query);
				else
					throw new StorageException(ex.Message, ex, query);
			}
		}

		/// <summary>
		/// Executes and compiles the <see cref="Opf3.Query.IQuery">IQuery</see> and returns
		/// an <see cref="Opf3.ObjectReader{T}">ObjectReader</see> with the resultsset.
		/// </summary>
		/// <param name="context">
		/// 	<see cref="Opf3.ObjectContext">ObjectContext</see> that is connected with the
		/// <see cref="Opf3.ObjectReader{T}">ObjectReader</see>.
		/// </param>
		/// <param name="query">
		/// 	<see cref="Opf3.Query.IQuery">IQuery</see> that is compiled and executed on the
		/// storage.
		/// </param>
		/// <exception cref="Opf3.Storages.StorageException">An unexpected storage exception has happened.</exception>
		public override ObjectReader<T> ExecuteReader<T>(ObjectContext context, IQuery query)
		{
			try
			{
				return base.ExecuteReader<T>(context, query);
			}
			catch (SqlException ex)
			{
				throw new StorageException(ex.Message, ex, query);
			}
		}

		/// <summary>Creates a new storage dependent connection object.</summary>
		/// <returns>Returns an instance of a storage dependent connection object.</returns>
		public override IDbConnection CreateConnection()
		{
			// Create the connection and open it.
			SqlConnection connection = new SqlConnection(this.ConnectionString);
			connection.Open();
			return connection;
		}

		/// <summary>Creates a new storage dependent command object.</summary>
		/// <returns>Returns an instance of a storage dependent command object.</returns>
		public override IDbCommand CreateCommand()
		{
			return new SqlCommand();
		}

		/// <summary>Creates and populates a storage dependent parameter with data.</summary>
		/// <returns>
		/// The name of the storage parameter should be set to a valid parameter name (one
		/// that is supported by the storage) while creating the parameter.
		/// </returns>
		/// <param name="index">Index of the storage parameter.</param>
		/// <param name="name">Name of the storage parameter.</param>
		/// <param name="value">Value of the storage parameter.</param>
		/// <param name="type">The type of the value argument. This information is useful if the value is null.</param>
		/// <exception cref="ArgumentNullException">Name is null.</exception>
		public override IDataParameter CreateParameter(int index, ref string name, object value, Type type)
		{
			if (name == null)
				throw new ArgumentNullException("name");

			// set the name for the parameter.
			name = "@" + name;

			if (value == null)
			{
				SqlDbType sqlDbType = SqlDbType.NVarChar;

				// convert to the sql server type.
				if (type == typeof(int)) sqlDbType = SqlDbType.Int;
				else if (type == typeof(short)) sqlDbType = SqlDbType.SmallInt;
				else if (type == typeof(long)) sqlDbType = SqlDbType.BigInt;
				else if (type == typeof(string)) sqlDbType = SqlDbType.NVarChar;
				else if (type == typeof(byte[])) sqlDbType = SqlDbType.Image;
				else if (type == typeof(bool)) sqlDbType = SqlDbType.Bit;
				else if (type == typeof(DateTime)) sqlDbType = SqlDbType.DateTime;
				else if (type == typeof(decimal)) sqlDbType = SqlDbType.Money;
				else if (type == typeof(double)) sqlDbType = SqlDbType.Float;
				else if (type == typeof(Single)) sqlDbType = SqlDbType.Real;
				else if (type == typeof(Guid)) sqlDbType = SqlDbType.UniqueIdentifier;
				else if (type == typeof(object)) sqlDbType = SqlDbType.Variant;

				SqlParameter parameter = new SqlParameter(name, sqlDbType);

				// use SqlMoney.Null if the type is money.
				if (sqlDbType == SqlDbType.Money)
					parameter.Value = SqlMoney.Null;
				else
					parameter.Value = DBNull.Value;

				return parameter;
			}

			string stringValue = value as string;
			// set the size of the string.
			if (stringValue != null)
			{
				SqlParameter p = new SqlParameter(name, stringValue);
				p.Size = stringValue.Length;
				// set the type to the nvarchar type.
				p.SqlDbType = SqlDbType.NVarChar;
				return p;
			}
			return new SqlParameter(name, value);
		}

		/// <summary>
		/// Starts a new transaction.
		/// </summary>
		/// <param name="level"><see cref="System.Data.IsolationLevel">IsolationLevel</see> of the transaction.</param>
		/// <param name="connection">The connection that is used to start the transaction.</param>
		/// <returns>Returns an instance of the <see cref="SqlTransaction">SqlTransaction</see> object.</returns>
		protected override SqlTransactionBase CreateTransaction(IsolationLevel level, IDbConnection connection)
		{
			return new MsSqlTransaction((SqlTransaction)connection.BeginTransaction(level));
		}

		/// <summary>
		/// Starts a new transaction.
		/// </summary>
		/// <param name="level"><see cref="System.Data.IsolationLevel">IsolationLevel</see> of the transaction.</param>
		/// <returns>Returns an instance of the <see cref="Transaction">Transaction</see> object.</returns>
		public override Transaction StartTransaction(IsolationLevel level)
		{
			lock (this)
			{
				// Check if an active transaction is available.
				Transaction transaction = this.ActiveTransaction;
				if (transaction != null)
				{
					// Create a sub transaction.
					return new MsSqlSubTransaction(((MsSqlTransaction)transaction).InnerTransaction as SqlTransaction);
				}
			}

			// Otherwise call the base method.
			return base.StartTransaction(level);
		}

		/// <summary>Transforms the parameter to a valid field.</summary>
		/// <returns>Returns a string that represents a valid field name.</returns>
		/// <remarks>
		/// This method returns a field name that is valid for the current storage. For some
		/// storages it is a better choice to start each field name with a '[' and end with a ']'
		/// (Ms Access) - This method could wrap each field name passed with '[]'.
		/// </remarks>
		/// <param name="name">Name for the field that is transformed.</param>
		/// <exception cref="ArgumentNullException">Name is null.</exception>
		public override string GetValidFieldName(string name)
		{
			if (name == null)
				throw new ArgumentNullException("name");

			return string.Format("[{0}]", name);
		}

		/// <summary>
		/// Transforms the name of the entity to a valid entity name.
		/// </summary>
		/// <returns>Returns a string that represents a valid entity name.</returns>
		/// <remarks>
		/// This method returns an entity name that is valid for the current storage. For
		/// some storages it is a better choice to start each entity name with a '[' and end with a
		/// ']' (Ms Access) - This method could wrap each entity name passed with '[]'.
		/// </remarks>
		/// <param name="name">Name of the entity.</param>
		/// <exception cref="ArgumentNullException">Name is null.</exception>
		public override string GetValidEntityName(string name)
		{
			if (name == null)
				throw new ArgumentNullException("name");

			return string.Format("[{0}]", name);
		}

		#endregion
	}
}